/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package introse1;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.PreparedStatement;
/**
 *
 * @author rootbeer
 */
public class MenuManager {
    public static PreparedStatement pstmt;
    public static DBConnect conn = new DBConnect();
    public static Connection pangkonek = conn.connect();
    public static VariableList variables;
    public static ResultSet res;
    public static ResultSet set;
    public static int original;
    
    public int checkExists(String a){
        try{
            pstmt = pangkonek.prepareStatement("Select name_of_food from recipe_file WHERE name_of_food = ?");
            pstmt.setString(1, a);
            pstmt.executeQuery();
            res = pstmt.getResultSet();
            if(!res.next()){
                return 0;
            }
            else{
                return 1;
            }
        }catch(Exception e){
            return -1;
        }
    }
    
    public void Deletetorecipelist(edit_menu e, String s1){
        variables = e.getVars();
        
        try{
            int x = -1;
            pstmt = pangkonek.prepareStatement("Select recipe_id FROM recipe_file WHERE name_of_food = ?");
            pstmt.setString(1, s1);
            res = pstmt.executeQuery();
            while(res.next()){
                x = Integer.parseInt(res.getString(1));
            }
            original = x;
            pstmt = pangkonek.prepareStatement("DELETE FROM recipe_file WHERE recipe_id = ?");
            pstmt.setInt(1, x);
            pstmt.executeUpdate();
            
            pstmt = pangkonek.prepareStatement("DELETE FROM recipe_ingredient WHERE recipe_id = ?");
            pstmt.setInt(1, x);
            pstmt.executeUpdate();
        }catch(Exception only){
            System.err.println("Error");
        }
    }
    
    public void EditToMenu(edit_menu e){
        variables = e.getVars();
        try{
            pstmt = pangkonek.prepareStatement("Insert INTO recipe_file VALUES(?,?,?)");
            pstmt.setInt(1, original);
            pstmt.setString(2,variables.getName_of_food());
            pstmt.setFloat(3, variables.getPrice_of_food());
            pstmt.executeUpdate();
        }catch(Exception ex){
            System.err.println("Error");
        }
    }
    
    public void AddToMenu(add_menu a){
        variables = a.getVars();
        try{
            pstmt = pangkonek.prepareStatement("Insert INTO recipe_file (name_of_food, price) VALUES(?,?)");
            pstmt.setString(1, variables.getName_of_food());
            pstmt.setFloat(2, variables.getPrice_of_food());
            pstmt.executeUpdate();
        }catch(Exception e){System.out.println("error while adding data to database");}
        
        
    }
    
    public ResultSet getIngredients(){
        try{
            pstmt = pangkonek.prepareStatement("Select inventoryName FROM inventory"); 
            res = pstmt.executeQuery();
            return res;
        }catch(Exception e){e.printStackTrace();
            return res;
        }
    }
   
    public ResultSet getMenuIngredients(String a){
        int x = -1;
        try{
            pstmt = pangkonek.prepareStatement("Select recipe_id FROM recipe_file WHERE name_of_food = ?");
            pstmt.setString(1, a);
            res = pstmt.executeQuery();
            while(res.next()){
                x = Integer.parseInt(res.getString(1));
            }
            pstmt = pangkonek.prepareStatement("Select * FROM recipe_ingredient WHERE recipe_id = ?");
            pstmt.setInt(1, x);
            res =  pstmt.executeQuery();
            while(res.next()){
                x = Integer.parseInt(res.getString("inventory_id"));
                pstmt = pangkonek.prepareStatement("Select * FROM inventory WHERE inventory_id = ?");
                pstmt.executeQuery();
            }
            return set;
        }catch(Exception e){
            return set;
        }
    }
    
    public ResultSet getMenu(){
        try{
            pstmt = pangkonek.prepareStatement("Select * FROM recipe_file");
            res = pstmt.executeQuery();
            return res;
        }catch(Exception e){
            return res;
        }
    }
    
    public void Edittorecipelist(edit_menu edit){
        variables = edit.getVars();
        try{
            int identify = -1;
            pstmt = pangkonek.prepareStatement("Select inventoryID FROM inventory WHERE inventoryName = ?");
            pstmt.setString(1, variables.getIngredient_name());
            res = pstmt.executeQuery();
            while(res.next()){
                identify = Integer.parseInt(res.getString(1));
            }
            
            pstmt = pangkonek.prepareStatement("INSERT INTO recipe_ingredient (recipe_id, inventory_id, quantity) VALUES(?,?,?)");
            pstmt.setInt(1, original);
            pstmt.setInt(2, identify);
            pstmt.setFloat(3, variables.getRecipe_quantity());
            pstmt.executeUpdate();
        }catch(Exception e){
        }
    }
    
    public void Delete(manage_menu m){
        variables = m.getListahan();
        try{
            
            int x = -1;
            pstmt = pangkonek.prepareStatement("Select recipe_id FROM recipe_file WHERE name_of_food = ?");
            pstmt.setString(1, variables.getName_of_food());
            res = pstmt.executeQuery();
            while(res.next()){
                x = Integer.parseInt(res.getString(1));
            }
            original = x;
            pstmt = pangkonek.prepareStatement("DELETE FROM recipe_file WHERE recipe_id = ?");
            pstmt.setInt(1, x);
            pstmt.executeUpdate();
            
            pstmt = pangkonek.prepareStatement("DELETE FROM recipe_ingredient WHERE recipe_id = ?");
            pstmt.setInt(1, x);
            pstmt.executeUpdate();
        }catch(Exception e){
            
        }
    }
    
    public void Addtorecipelist(add_menu a){
        variables = a.getVars();
        try{
            int x = -1;
            int inventoryid = -1;
            pstmt = pangkonek.prepareStatement("Select MAX(recipe_id) FROM recipe_file");
            pstmt.executeQuery();
            res = pstmt.getResultSet();
            while(res.next()){
                x = Integer.parseInt(res.getString(1));
            }
            pstmt = pangkonek.prepareStatement("Select inventoryID FROM inventory WHERE inventoryName = ?");
            pstmt.setString(1, variables.getIngredient_name());
            res = pstmt.executeQuery();
            while(res.next()){
                inventoryid = Integer.parseInt(res.getString(1));
            }
            pstmt = pangkonek.prepareStatement("Insert INTO recipe_ingredient (recipe_id, inventory_id, quantity) VALUES(?,?,?)");
            pstmt.setInt(1,x);
            pstmt.setInt(2, inventoryid);
            pstmt.setFloat(3,variables.getRecipe_quantity());
            pstmt.executeUpdate();    
        }catch(Exception e){System.out.println("errorsssS");}
    }
}
